Database query optimisation based on measures of regret

The query optimiser in a database management system (DBMS) is responsible for �nding a good order in which to execute the operators in a given query. However, in practice the query optimiser does not usually guarantee to �nd the best plan. This is often due to the non-availability of precise statistical data or inaccurate assumptions made by the optimiser. In this thesis we propose a robust approach to logical query optimisation that takes into account the unreliability in database statistics during the optimisation process. In particular, we study the ordering problem for selection operators and for join operators, where selectivities are modelled as intervals rather than exact values. As a measure of optimality, we use a concept from decision theory called minmax regret optimisation (MRO). When using interval selectivities, the decision problem for selection operator ordering turns out to be NP-hard. After investigating properties of the problem and identifying special cases which can be solved in polynomial time, we develop a novel heuristic for solving the general selection ordering problem in polynomial time. Experimental evaluation of the heuristic using synthetic data, the Star Schema Benchmark and real-world data sets shows that it outperforms other heuristics (which take an optimistic, pessimistic or midpoint approach) and also produces plans whose regret is on average very close to optimal. The general join ordering problem is known to be NP-hard, even for exact selectivities. So, for interval selectivities, we restrict our investigation to sets of join operators which form a chain and to plans that correspond to left-deep join trees. We investigate properties of the problem and use these, along with ideas from the selection ordering heuristic and other algorithms in the literature, to develop a polynomial-time heuristic tailored for the join ordering problem. Experimental evaluation of the heuristic shows that, once again, it performs better than the optimistic, pessimistic and midpoint heuristics. In addition, the results show that the heuristic produces plans whose regret is on average even closer to the optimal than for selection ordering

Database query optimisation based on measures of regret

The query optimiser in a database management system (DBMS) is responsible for �nding a good order in which to execute the operators in a given query. However, in practice the query optimiser does not usually guarantee to �nd the best plan. This is often due to the non-availability of precise statistical data or inaccurate assumptions made by the optimiser. In this thesis we propose a robust approach to logical query optimisation that takes into account the unreliability in database statistics during the optimisation process. In particular, we study the ordering problem for selection operators and for join operators, where selectivities are modelled as intervals rather than exact values. As a measure of optimality, we use a concept from decision theory called minmax regret optimisation (MRO). When using interval selectivities, the decision problem for selection operator ordering turns out to be NP-hard. After investigating properties of the problem and identifying special cases which can be solved in polynomial time, we develop a novel heuristic for solving the general selection ordering problem in polynomial time. Experimental evaluation of the heuristic using synthetic data, the Star Schema Benchmark and real-world data sets shows that it outperforms other heuristics (which take an optimistic, pessimistic or midpoint approach) and also produces plans whose regret is on average very close to optimal. The general join ordering problem is known to be NP-hard, even for exact selectivities. So, for interval selectivities, we restrict our investigation to sets of join operators which form a chain and to plans that correspond to left-deep join trees. We investigate properties of the problem and use these, along with ideas from the selection ordering heuristic and other algorithms in the literature, to develop a polynomial-time heuristic tailored for the join ordering problem. Experimental evaluation of the heuristic shows that, once again, it performs better than the optimistic, pessimistic and midpoint heuristics. In addition, the results show that the heuristic produces plans whose regret is on average even closer to the optimal than for selection ordering

Capuchin Search Algorithm With Deep Learning-Based Data Edge Verification for Blockchain-Assisted IoT Environment

Internet of Things (IoT) devices generate enormous quantities of data, and ensuring the authenticity and integrity of this data is essential. Blockchain (BC) serves as a transparent and secure ledger for recording each data transaction, which prevents unauthorized modification and provides a trust layer for the IoT ecosystem. Data edge verification for BC-enabled IoT platforms is a fundamental aspect of ensuring data trustworthiness, integrity, and security in the system. In such an environment, IoT devices generate vast amounts of data, and BC technology can be used to create a decentralized and immutable ledger that records all the transactions and data exchanges Machine learning (ML) in the context of BC and the IoT offers a powerful toolkit for optimizing and securing these technologies. It enables the analysis of vast and complex data generated by IoT devices, allowing for predictive maintenance, anomaly detection, and pattern recognition. ML also enhances security by developing intrusion detection systems and supporting smart contracts in BC networks. This article introduces a new Capuchin Search Algorithm with a Deep Learning based Data Edge Verification model (CSADL-DEVM) for the Blockchain assisted IoT platform. The purpose of the CSADL-DEVM technique is to integrate the BC with DL and hyperparameter tuning concepts for data edge verification in the IoT environment. In addition, IoT devices comprise a considerable level of decentralized decision-making ability, which accomplishes a consensus on the performance of intra-block transactions. In addition, the CSADL-DEVM technique applies the Elman recurrent neural network (ERNN) model for the identification and classification of faults. Moreover, the hyperparameters related to the ERNN model can be adjusted by the use of CSA which in turn better the detection results. A comprehensive set of simulations has been conducted to exhibit the enhanced results of the CSADL-DEVM method. The obtained outcomes exhibit the superior outcome of the CSADL-DEVM algorithm